CREATE VIEW [dbo].[vLegacySecurityRole]
AS
SELECT a.[UserId], c.[UserKey], 'CustomerLevel' + CAST(b.[LevelCode] AS varchar) AS Role
  FROM [dbo].[Users] AS a
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.[LevelMembership] >= b.[LevelCode]
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]
UNION
SELECT a.[UserId], c.[UserKey], 'BillingLevel' + CAST(b.[LevelCode] AS varchar) AS Role
  FROM [dbo].[Users] AS a
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.[LevelDues] >= b.[LevelCode]
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]
UNION
SELECT a.[UserId], c.[UserKey], 'CashARLevel' + CAST(b.[LevelCode] AS varchar) AS Role
  FROM [dbo].[Users] AS a
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.[LevelCashAR] >= b.[LevelCode]  
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]
UNION
SELECT a.[UserId], c.[UserKey], 'EventsLevel' + CAST(b.[LevelCode] AS varchar) AS Role
  FROM [dbo].[Users] AS a  
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.[LevelMeeting] >= b.[LevelCode]  
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]
UNION
SELECT a.[UserId], c.[UserKey], 'OrdersLevel' + CAST(b.[LevelCode] AS varchar) AS Role
  FROM [dbo].[Users] AS a  
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.[LevelOrderEntry] >= b.[LevelCode]  
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]
UNION
SELECT a.[UserId], c.[UserKey], 'ReferralLevel' + CAST(b.[LevelCode] AS varchar) AS Role
  FROM [dbo].[Users] AS a  
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.LevelReferral >= b.[LevelCode]  
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]
UNION
SELECT a.[UserId], c.[UserKey], 'CertificationLevel' + CAST(b.[LevelCode] AS varchar) AS Role
  FROM [dbo].[Users] AS a  
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.[LevelCert] >= b.[LevelCode]  
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]
UNION
SELECT a.[UserId], c.[UserKey], 'FundraisingLevel' + CAST(b.[LevelCode] AS varchar) AS Role
  FROM [dbo].[Users] AS a  
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.[LevelFundRaising] >= b.[LevelCode]  
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]
UNION
SELECT a.[UserId], c.[UserKey], 'ServiceCentralLevel' + CAST(b.[LevelCode] AS varchar) AS Role
  FROM [dbo].[Users] AS a  
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.[LevelSC] >= b.[LevelCode]  
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]
UNION
SELECT a.[UserId], c.[UserKey], 'ExpoLevel' + CAST(b.[LevelCode] AS varchar) AS Role
  FROM [dbo].[Users] AS a  
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.[LevelExpo] >= b.[LevelCode]  
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]
UNION
SELECT a.[UserId], c.[UserKey], 'SysAdmin' AS Role
  FROM [dbo].[Users] AS a  
       INNER JOIN [dbo].[LegacySecurityLevelRef] AS b ON a.[LevelSystem] >= 8  
       INNER JOIN [dbo].[UserMain] AS c ON a.[UserId] = c.[UserId]

GO